﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using MSExcel = Microsoft.Office.Interop.Excel;
using System.Data;
using SASystem.SAServiceRef;

namespace SASystem
{
    public class ExcelServiceMethod
    {
        public static string lastErrMsg;
        private static string EXCELOBJ = "Excel.Application";
        private static Microsoft.Office.Interop.Excel.Application m_ExcelApp;
        
        //define events.
        public delegate void StartReadEventHandler(Object sender, EventArgs e);
        public static event StartReadEventHandler RaiseStartReadEvent;

        public delegate void EndReadEventHandler(Object sender, EventArgs e);
        public static event EndReadEventHandler RaiseEndReadEvent;

        public delegate void StepEventHandler(Object sender, EventArgs e);
        public static event StepEventHandler RaiseStepEvent;

        private static Microsoft.Office.Interop.Excel.Application GetExcelApp()
        {
            //try to get if not exist.
            if (m_ExcelApp == null)
            {
                try
                {   //try to get the object.
                    m_ExcelApp = (MSExcel.Application)Marshal.GetActiveObject(EXCELOBJ);
                }
                catch (System.Runtime.InteropServices.COMException ex)
                {
                    lastErrMsg = ex.Message;
                }
                catch (Exception ex)
                {
                    lastErrMsg = ex.Message;
                }
            }

            //try to create if not exist.
            if (m_ExcelApp == null)
            {
                try
                {
                    System.Type appType = System.Type.GetTypeFromProgID(EXCELOBJ);
                    m_ExcelApp = (Microsoft.Office.Interop.Excel.Application)System.Activator.CreateInstance(appType);
                }
                catch (Exception ex)
                {
                    lastErrMsg = ex.Message;
                }
            }

            return m_ExcelApp;
        }

        //return parameter array in order.
        //
        public static List<ParameterInfo> GetExcelParameters(string excelFilePath)
        {
            List<ParameterInfo> retList = new List<ParameterInfo>();

            MSExcel.Application excelApp = GetExcelApp();

            if (excelApp == null)
            {
                //last error is assigned.
                return null;
            }

            try
            {
                excelApp.Workbooks.Close();
                MSExcel.Workbook workbook = excelApp.Workbooks.Open(excelFilePath,Type.Missing, true);
                MSExcel.Worksheet worksheet = (MSExcel.Worksheet)workbook.Worksheets[1];

                int columnCnt = worksheet.UsedRange.Columns.Count;

                for (int i = 1; i <= columnCnt; i++)
                {
                    string title = Convert.ToString(((MSExcel.Range)worksheet.Cells[1, i]).Value);
                    ParameterInfo newParam = new ParameterInfo();
                    newParam.Name = title;
                    retList.Add(newParam);
                }

                if (workbook != null)
                {
                    workbook.Close(false);
                }
            }
            catch (Exception ex)
            {
                lastErrMsg = ex.Message;
                return null;
            }

            return retList;
        }

        public static DataTable GetExcelTable(string excelFilePath)
        {
            DataTable retTable = new DataTable();

           MSExcel.Application excelApp = GetExcelApp();

            if (excelApp == null)
            {
                //last error is assigned.
                return null;
            }

            //invoke event.
            if (RaiseStartReadEvent != null) 
                RaiseStartReadEvent(excelApp, new EventArgs());
            
            try
            {
                //excelApp.Workbooks.Close();
                MSExcel.Workbook workbook = excelApp.Workbooks.Open(excelFilePath);
                MSExcel.Worksheet worksheet = (MSExcel.Worksheet)workbook.Worksheets[1];

                int columnCnt = worksheet.UsedRange.Columns.Count;
                int rowCnt = worksheet.UsedRange.Rows.Count;

                for (int i = 1; i <= columnCnt; i++)
                {
                    string title = Convert.ToString(((MSExcel.Range)worksheet.Cells[1, i]).Value);

                    retTable.Columns.Add(title, System.Type.GetType("System.String"));
                }

                int rowsPerStep = rowCnt / 100;
                int curStep = 0;
                for (int i = 2; i <= rowCnt; i++)
                {
                    DataRow newRow = retTable.NewRow();
                    for (int j = 1; j <= columnCnt; j++)
                    {
                        string cellVal = Convert.ToString(((MSExcel.Range)worksheet.Cells[i, j]).Value);
                        newRow[j - 1] = cellVal;
                    }
                    retTable.Rows.Add(newRow);

                    //raise step event.
                    if (i > rowsPerStep * curStep)
                    {
                        curStep++;

                        if (RaiseStepEvent!= null)
                            RaiseStepEvent(excelApp, new EventArgs());
                    }
                }
                workbook.Close(false);
            }
            catch (Exception ex)
            {
                lastErrMsg = ex.Message;
                return null;
            }
            finally
            {
                //invoke event.
                if (RaiseEndReadEvent != null)
                    RaiseEndReadEvent(excelApp, new EventArgs());
            }

            return retTable;
        }
    }
}
